﻿/*****************************************************************************/
/* Stored Procedure :: [SaveCaseNote]										 */
/*****************************************************************************/
/* Copyright (C) 2003, Perot Systems Corporation. All right reserved.        */
/* Developer: Ed. Mueller <edward.mueller@ps.net>                            */
/* Started:  10/03/2008                                                      */
/*****************************************************************************/
CREATE PROCEDURE [NoteManagement].[SaveCaseNote]
(
    @OID bigint,
    @Version timestamp,
    @CaseID bigint,
    @Notes nvarchar( 3000 ),
    @AuthorID uniqueidentifier
)
AS 
BEGIN
  DECLARE @NewOID bigint
  -- 1. If new record (OID is not set)
  IF ISNULL( @oid, 0 ) <= 0 
  BEGIN
    -- 1.1. Perform insert (OID is identity)
    INSERT INTO CaseNotes ( CaseID, Notes, AuthorID, UpdatedDate )
    VALUES ( @CaseID, @Notes, @AuthorID, GetDate() )
    -- 1.2. Determine OID of newly inserted object
    SET @NewOID =  SCOPE_IDENTITY()
    -- 1.3. Retutn OID and timestamp of newly inserted object
    SELECT OID, Version 
    FROM CaseNotes 
    WHERE OID = @NewOID
  END
  -- 2. If OID is set (existing record)
  ELSE
  BEGIN
    -- 2.1. Attempt updating object (using both OID and timestamp for concurrency reasons)
    UPDATE CaseNotes
    SET CaseID = @CaseID,
		Notes = @Notes,
		AuthorID = @AuthorID,
		UpdatedDate = GetDate()
    WHERE ( ( OID = @OID ) AND ( Version = @Version ) )
    -- 2.2. If no records were found (and updated) - it means that
    -- another process had updated this record (therefore timestamp will be different)
    -- or record was deleted (OID not found)
    -- Either way, we need to raise exception
    IF @@ROWCOUNT = 0
    BEGIN
      RAISERROR( 99999, 16, 1, 'CaseNotes' ) --WITH LOG
    END
    -- 2.3. Record was found - return OID and new timestamp
    ELSE
    BEGIN 
      SELECT OID, Version
      FROM CaseNotes
      WHERE OID = @OID
    END
  END
END
